﻿<%@ WebHandler Language="C#" Class="WorkToolsPrepareReport" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Net;
using ReportBusiness;

/**
 * Description: 工装工具准备查询报表
 * Copyright (c) : 通力凯顿（北京）系统集成有限公司
 * Writer:zhunan
 * Date:2022.4.15
 **/

public class WorkToolsPrepareReport : IHttpHandler
{
    private uMESReportOracleDAL dal = new uMESReportOracleDAL();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getData":
                        r = getData(jObject);//查询
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportExcel":
                        exportExcel(jObject);//导出
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    string getSearchSqlString(JObject jObject)
    {
        string factoryid = "", workcenterid = "", teamid = "";
        JArray selectOrg = JArray.Parse(jObject["selectOrg"].ToString());
        if (selectOrg.Count == 3)
        {
            factoryid = selectOrg[0].ToString();
            workcenterid = selectOrg[1].ToString();
            teamid = selectOrg[2].ToString();
        }
        else if (selectOrg.Count == 2)
        {
            factoryid = selectOrg[0].ToString();
            workcenterid = selectOrg[1].ToString();
        }
        else
        {
            factoryid = selectOrg[0].ToString();
        }
        string sql = @"
SELECT cci.productname,cci.containername,cci.fromworkcentername,ws.workflowstepname,s.specno,spb.stepinfoname,sp.stepno,
       ag.toolno,ag.toolname,
       te.drawingno,te.tooltype,te.specification,
       ag.qty needqty,ag.needdate,w.workcentername,
       t.teamname,e.fullname useperson,ag.createdate,ag.toolstatus,
       DECODE(ag.toolstatus,'待准备',ROUND((SYSDATE-ag.needdate),1),0) delaydays
FROM arrangements ag
LEFT JOIN containercurrentinfo cci ON ag.containerid = cci.containerid
LEFT JOIN Workflowstep ws ON ws.workflowstepid = ag.workflowstepid
LEFT JOIN specbase sb ON sb.specbaseid = ws.specbaseid 
LEFT JOIN spec s ON s.specid = NVL(sb.revofrcdid,ws.specid)
LEFT JOIN sessionvalues sv ON sv.employeeid = ag.employeeid
LEFT JOIN Workcenter w ON w.workcenterid = sv.workcenterid
LEFT JOIN team t ON t.teamid = ag.teamid
LEFT JOIN employee e ON ag.employeeid = e.employeeid
LEFT JOIN 
(
     SELECT '' AS stepinfoid,wst.workflowstepid,NVL(NVL(NVL(ct.cuttoolname,mt.measuretoolname),st.specialtoolname),am.auxmaterialname) toolno,
            NVL(NVL(ct.drawingno, mt.drawingno), st.drawingno) drawingno,
            NVL(NVL(NVL(ct.specification, mt.specification), st.specification),am.specification) specification,
            CASE WHEN ct.cuttoolid IS NOT NULL THEN '刀具'
                 WHEN mt.measuretoolid IS NOT NULL THEN '量具'
                 WHEN st.specialtoolid IS NOT NULL THEN '夹模具'
                 WHEN am.auxmaterialid IS NOT NULL THEN '辅材'
            END  tooltype
     FROM Workflowsteptools wst
     LEFT JOIN cuttool ct ON ct.cuttoolid = wst.tools
     LEFT JOIN MeasureTool mt ON mt.measuretoolid = wst.tools
     LEFT JOIN SpecialTool st ON st.specialtoolid = wst.tools
     LEFT JOIN auxmaterial am ON am.auxmaterialid = wst.tools
     UNION ALL
     SELECT sp.stepinfoid,sp.workflowstepid,NVL(NVL(NVL(ct.cuttoolname,mt.measuretoolname),st.specialtoolname),am.auxmaterialname) toolno,
            NVL(NVL(ct.drawingno, mt.drawingno), st.drawingno) drawingno,
            NVL(NVL(NVL(ct.specification, mt.specification), st.specification),am.specification) specification,
            CASE WHEN ct.cuttoolid IS NOT NULL THEN '刀具'
                 WHEN mt.measuretoolid IS NOT NULL THEN '量具'
                 WHEN st.specialtoolid IS NOT NULL THEN '夹模具'
                 WHEN am.auxmaterialid IS NOT NULL THEN '辅材'
            END  tooltype
     FROM stepinfotools sit
     LEFT JOIN stepinfo sp ON sp.stepinfoid = sit.stepinfoid
     LEFT JOIN stepinfobase spb ON spb.stepinfobaseid = sp.stepinfobaseid
     LEFT JOIN cuttool ct ON ct.cuttoolid = sit.tools
     LEFT JOIN MeasureTool mt ON mt.measuretoolid = sit.tools
     LEFT JOIN SpecialTool st ON st.specialtoolid = sit.tools
     LEFT JOIN auxmaterial am ON am.auxmaterialid = sit.tools
) te ON te.workflowstepid || te.stepinfoid = ag.workflowstepid || ag.stepinfoid AND te.toolno = ag.toolno
LEFT JOIN stepinfo sp ON sp.stepinfoid = ag.stepinfoid
LEFT JOIN stepinfobase spb ON spb.stepinfobaseid = sp.stepinfobaseid
WHERE cci.containerstatus = 1 ";
        //主制分厂
        if (factoryid != "")
        {
            sql += string.Format(" AND cci.originalfactoryid = '{0}'", factoryid);
        }

        //主制工区
        if (workcenterid != "")
        {
            sql += string.Format(" AND cci.fromworkcenterid = '{0}'", workcenterid);
        }

        //班组
        if (teamid != "")
        {
            sql += string.Format(" AND t.teamid = '{0}'", teamid);
        }

        //使用人
        if (jObject["appler"] != null && jObject["appler"].ToString() != "")
        {
            sql += string.Format(" AND e.employeeid = '{0}'", jObject["appler"].ToString());
        }

        //零件图号
        if (jObject["productname"] != null && jObject["productname"].ToString() != "")
        {
            sql += string.Format(" AND cci.productname = '{0}'", jObject["productname"].ToString());
        }

        //流水卡号
        if (jObject["containername"] != null && jObject["containername"].ToString() != "")
        {
            sql += string.Format(" AND cci.containername = '{0}'", jObject["containername"].ToString());
        }

        //状态
        if (jObject["status"] != null && jObject["status"].ToString() != "")
        {
            if (jObject["status"].ToString() == "待准备")
            {
                sql += " AND ag.toolstatus = '待准备'";
            }
            else if (jObject["status"].ToString() == "待配送")
            {
                sql += " AND ag.toolstatus LIKE '%待配送%'";
            }
            else
            {
                sql += " AND ag.toolstatus = '已配送'";
            }
        }

        //申请工区
        if (jObject["applyworkcenterid"] != null && jObject["applyworkcenterid"].ToString() != "")
        {
            sql += string.Format(" AND w.workcenterid = '{0}'", jObject["applyworkcenterid"].ToString());
        }

        //申请时间
        if (jObject["createdate"] != null && jObject["createdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["createdate"].ToString());
            if (arr.Count == 2)
            {
                sql += string.Format(" AND TRUNC(ag.createdate) BETWEEN TO_DATE('{0}','yyyy-mm-dd') and TO_DATE('{1}','yyyy-mm-dd')", arr[0].ToString(), arr[1].ToString());
            }
        }

        //类型
        if (jObject["tooltype"] != null && jObject["tooltype"].ToString() != "")
        {
            string tooltype = jObject["tooltype"].ToString();
            if (tooltype == "刀具")
            {
                sql += " AND te.tooltype = '刀具'";
            }
            else if (tooltype == "量具")
            {
                sql += " AND te.tooltype = '量具'";
            }
            else if (tooltype == "夹模具")
            {
                sql += " AND te.tooltype = '夹模具'";
            }
            else
            {
                sql += " AND te.tooltype = '辅材'";
            }
        }
        return sql;
    }

    //查询方法
    Result getData(JObject jObject)
    {
        string sql = getSearchSqlString(jObject);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;
        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        foreach (DataRow dr in dt.Rows)
        {
            if (Convert.ToDouble(dr["delaydays"].ToString()) < 0)
            {
                dr["delaydays"] = 0;
            }
        }
        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //导出
    void exportExcel(JObject jObject)
    {
        string sql = getSearchSqlString(jObject);
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        foreach (DataRow dr in dtResult.Rows)
        {
            if (Convert.ToDouble(dr["delaydays"].ToString()) < 0)
            {
                dr["delaydays"] = 0;
            }
        }
        dtResult.Columns["productname"].Caption = "零件图号";
        dtResult.Columns["containername"].Caption = "流水卡号";
        dtResult.Columns["fromworkcentername"].Caption = "主制工区";
        dtResult.Columns["specno"].Caption = "工序号";
        dtResult.Columns["toolno"].Caption = "工具编号";
        dtResult.Columns["toolname"].Caption = "工具名称";
        dtResult.Columns["drawingno"].Caption = "编码/规格";
        dtResult.Columns["tooltype"].Caption = "类型";
        dtResult.Columns["needqty"].Caption = "需求数量";
        dtResult.Columns["needdate"].Caption = "需求日期";
        dtResult.Columns["workcentername"].Caption = "申请工区";
        dtResult.Columns["teamname"].Caption = "班组";
        dtResult.Columns["useperson"].Caption = "使用人";
        dtResult.Columns["createdate"].Caption = "申请时间";
        dtResult.Columns["toolstatus"].Caption = "准备情况";
        dtResult.Columns["delaydays"].Caption = "超期时间";
        dtResult.Columns["workflowstepname"].Caption = "工序名称";
        dtResult.Columns["stepinfoname"].Caption = "工步名称";
        dtResult.Columns["stepno"].Caption = "工步号";
        dtResult.Columns["specification"].Caption = "规格";
        ExportHelper.ExportExcel(dtResult, "工装工具准备查询报表");
    }
}